package co.yixiang.modules.shop.utils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;


/***
 * excel 导出工具类
 * @author Administrator
 *
 */
public class ExcelUtil {
    /**
     * @param headers      标题集合 tilte的长度应该与list中的model的属性个数一致
     * @param dataset      内容集合
     * @param mergeColumns 合并单元格的列
     */
    public static void createExcel(HttpServletResponse response, String[] headers, List<Map<String, String>> dataset, String[] mergeColumns, String data) throws Exception {

        /*初始化excel模板*/
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = null;
        try {
            sheet = workbook.createSheet();
        } catch (Exception e) {
            e.printStackTrace();
        }

        /*初始化head，填值标题行（第一行）*/
        Row row0 = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            /*创建单元格，指定类型*/
            Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
            cell_1.setCellValue(headers[i]);
        }

        List<PoiModel> poiModels = new ArrayList<PoiModel>();
        Iterator<Map<String, String>> iterator = dataset.iterator();

        int index = 1;
        while (iterator.hasNext()) {
            //这里是从excel的第三行开始，第二行已经塞入标题了
            Row row = sheet.createRow(index);
            // 设置默认列宽
            sheet.setDefaultColumnWidth(16);
            sheet.setDefaultRowHeightInPoints(22);
            sheet.autoSizeColumn(1, true);
            // 取得当前这行的map，该map中以key，value的形式存着这一行值
            Map<String, String> map = iterator.next();
            // 循环列数，给当前行塞值
            for (int i = 0; i < headers.length; i++) {
                String old = "";
                // old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记
                if (index > 1) {
                    old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
                }

                String value = map.get(headers[i]);
                CellRangeAddress cra = null;
                // 循环需要合并的列
                for (int j = 0; j < mergeColumns.length; j++) {
                    PoiModel poiModel = null;
                    if (index == 1) {
                        poiModel = new PoiModel();
                        poiModel.setOldContent(value);
                        poiModel.setContent(value);
                        poiModel.setRowIndex(1);
                        poiModel.setCellIndex(i);
                        poiModels.add(poiModel);
                        old = value;
                        break;
                    }
                    poiModel = poiModels.get(i);

                    int rowStartIndex = poiModel.getRowIndex();
                    int rowEndIndex = index - 1;
                    int cellIndex = poiModel.getCellIndex();
                    String content = poiModel.getContent();
                    String preOldContent = poiModels.get(0).getOldContent();
                    String preValue = map.get(headers[0]);
                    Boolean isHeaderEquals = mergeColumns[j].equals(headers[i]);

                    if (i == 0 && isHeaderEquals && !content.equals(value)) {
                        if (rowStartIndex != rowEndIndex) {
                            cra = new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);
                            sheet.addMergedRegion(cra);
                        }
                        // 重新记录该列的内容为当前内容，行标记改为当前行标记
                        poiModel.setContent(value);
                        poiModel.setRowIndex(index);
                        poiModel.setCellIndex(i);
                    } else if (i > 0 && isHeaderEquals) {
                        if (!content.equals(value) || (content.equals(value) && !preOldContent.equals(preValue))) {
                            if (rowStartIndex != rowEndIndex) {
                                cra = new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);
                                sheet.addMergedRegion(cra);
                            }
                            poiModels.get(i).setContent(value);
                            poiModels.get(i).setRowIndex(index);
                            poiModels.get(i).setCellIndex(i);
                        }
                    }
                    if (isHeaderEquals && index == dataset.size()) {
                        if (i == 0) {
                            if (content.equals(value)) {
                                cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);
                                sheet.addMergedRegion(cra);
                            }
                        } else if (i > 0) {
                            if (content.equals(value) && preOldContent.equals(preValue)) {
                                cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);
                                sheet.addMergedRegion(cra);
                            }
                        }
                    }
                }
                Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                cell.setCellValue(value);
                // 在每一个单元格处理完成后，把这个单元格内容设置为old内容
                poiModels.get(i).setOldContent(old);
            }
            index++;
        }

        /*生成临时文件*/
        FileOutputStream out = null;
        String localPath = null;
        File tempFile = null;
        String fileName = String.valueOf(System.currentTimeMillis() / 1000);
        tempFile = File.createTempFile(fileName, ".xlsx");
        localPath = tempFile.getAbsolutePath();
        out = new FileOutputStream(localPath);
        workbook.write(out);
        out.flush();
        out.close();
        //合并完成后，对文件插入一行
        insertRow(localPath, data);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");
        OutputStream os = response.getOutputStream();

        byte[] buf = new byte[8192];
        int len;
        File file=new File(localPath);
        InputStream inputStream = new FileInputStream(file);
        BufferedInputStream bis = new BufferedInputStream(inputStream);
        while ((len = bis.read(buf)) > 0) {
            os.write(buf, 0, len);
        }
        if (inputStream != null) {
            inputStream.close();
        }
        os.flush();
        os.close();
        //完成文件删除临时文件
        file.delete();
    }

    /**
     * 创建单元格样式
     *
     * @param workbook 工作簿
     * @param fontSize 字体大小
     * @return 单元格样式
     */
    private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontSize) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        // 创建字体
        XSSFFont font = workbook.createFont();
        font.setBold(true);
//        font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗字体
        font.setFontHeightInPoints(fontSize);
        // 加载字体
        style.setFont(font);
        return style;
    }

    /**
     * Excel插入行
     *
     * @param fileName
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void insertRow(String fileName, String date) throws IOException, InvalidFormatException {
        File file = new File(fileName);
        Workbook workbook = new XSSFWorkbook(new FileInputStream(file));
        Sheet sheet = workbook.getSheetAt(0);
        //在第一行中插入一行，原文件第一行变第二行
        sheet.shiftRows(0, sheet.getLastRowNum(), 1);
        //创建一行
        Row newRow = sheet.createRow(0);
        //第一行第一列
        Cell newCell = newRow.createCell(0, 0);
        newCell.setCellValue("盘点时间:");
        //第一行第2列
        Cell newCell2 = newRow.createCell(1, 0);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 1, 3);// 起始行号，结束行号，起始列号，结束列号
        //创建大标题样式
        newCell2.setCellValue(date);
        sheet.addMergedRegion(cellRangeAddress);
        workbook.write(new FileOutputStream(fileName));
    }

    public static void main(String[] args) throws Exception {
        //StoreProductController类中exportStockTaking方法调用
    }


}